package com.kerrykidz.system.util.sync;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;

import com.kerrykidz.common.PrimaryKeyGenerator;
import com.kerrykidz.system.util.sync.SyncBean.OrderBy;
import com.rdp.util.config.SpringUtil;

public class SqlHelper {

	public static final String EXTRACT_FLAG_YES = "1";
	public static final String EXTRACT_FLAG_NO = "0";
	public static final String SYNC_STATUS_SUCCESS = "1";
	public static final String SYNC_STATUS_FAIL = "2";

	public static final List<SyncBean> CONSTANTLY = new ArrayList<SyncBean>();

	public static final List<SyncBean> EVERY_DAY = new ArrayList<SyncBean>();

	public static final String CONDITION_MODI_TIME = "MODI_TIME";

	public static final String CONDITION_OPERATE_TIME = "OPERATE_TIME";

	public static final String SQL_SELECT_SYS_DATA_SYNC = "SELECT * FROM SYS_DATA_SYNC WHERE FK_BRAN_ID=? AND EXTRACT_FLAG = ? ORDER BY MODI_TIME ASC";

	public static final String SYS_DATA_SYNC_FLDS = "PK_DATASYNC_ID,TABLE_NAME,SYNC_TIME_S,SYNC_TIME_E,SYNC_CNT,EXTRACT_FLAG,FK_BRAN_ID,MODI_TIME";

	public static final String SQL_INSERT_SYS_DATA_SYNC_LOG = "INSERT INTO SYS_DATA_SYNC_LOG(DATASYNC_ID,SYNC_STATUS,ERR_INFO,SERIAL_ID,FK_BRAN_ID,MODI_TIME)VALUES(?,?,?,?,?,?)";

	public static final String SQL_SELECT_SYS_DATA_SYNC_LOG = "SELECT * FROM SYS_DATA_SYNC_LOG WHERE DATASYNC_ID = ?";

	public static final String SQL_UPDATE_SYS_DATA_SYNC_LOG = "UPDATE SYS_DATA_SYNC_LOG SET SYNC_STATUS=1 WHERE DATASYNC_ID=? ";

	public static final String SQL_SELECT_NOT_OPERATE_SYS_DATA_SYNC_LOG = "SELECT * FROM SYS_DATA_SYNC_LOG WHERE  SYNC_STATUS = '2' ORDER BY MODI_TIME ASC";

	/**
	 * 实时同步表
	 */
	static {
		SyncBean role = new SyncBean("SEC_ROLE", "PK_ROLE_ID", SyncBean.SYNCTYPE_MD_ZB);
		SyncBean usr = new SyncBean("SEC_USR", "PK_USR_ID", SyncBean.SYNCTYPE_MD_ZB);
		List<SyncBean> userrelations = new ArrayList<SyncBean>();

		SyncBean role2usr = new SyncBean("SEC_ROLE2USR", "FK_USR_ID", "PK_USR_ID");
		SyncBean obj2usr = new SyncBean("SEC_OBJ2USR", "FK_USR_ID", "PK_USR_ID");

		userrelations.add(role2usr);
		userrelations.add(obj2usr);
		usr.setRelations(userrelations);

		List<SyncBean> rolerelations = new ArrayList<SyncBean>();
		SyncBean role2obj = new SyncBean("SEC_ROLE2OBJ", "FK_ROLE_ID", "PK_ROLE_ID");
		rolerelations.add(role2obj);
		role.setRelations(rolerelations);

		SyncBean prdUnit = new SyncBean("GBL_SCM_PRD_UNIT", "PK_PRDUNIT_ID", SyncBean.SYNCTYPE_MD_ZB);
		SyncBean prdCate = new SyncBean("GBL_SCM_PRD_CATE", "PK_PRDCATE_ID", SyncBean.SYNCTYPE_MD_ZB);
		SyncBean prd = new SyncBean("GBL_SCM_PRD", "PK_PRD_ID", SyncBean.SYNCTYPE_ZB);

		List<SyncBean> prdrelations = new ArrayList<SyncBean>();

		SyncBean prdMem = new SyncBean("GBL_SCM_PRD_MEM", "FK_PRD_ID", "PK_PRD_ID");
		SyncBean prdDpass = new SyncBean("GBL_SCM_PRD_DPASS", "FK_PRD_ID", "PK_PRD_ID");
		SyncBean prdPar = new SyncBean("GBL_SCM_PRD_PAR", "FK_PRD_ID", "PK_PRD_ID");
		SyncBean prdClass = new SyncBean("GBL_SCM_PRD_CLASS", "FK_PRD_ID", "PK_PRD_ID");
		SyncBean stg = new SyncBean("SCM_PRD_STG", "FK_PRD_ID", "PK_PRD_ID");

		prdrelations.add(prdMem);
		prdrelations.add(prdDpass);
		prdrelations.add(prdPar);
		prdrelations.add(prdClass);
		prdrelations.add(stg);
		prd.setRelations(prdrelations);

		SyncBean gblprdPrice = new SyncBean("GBL_SCM_PRD_PRICE", "PK_PRICE_ID", SyncBean.SYNCTYPE_MD_ZB);

		SyncBean sup = new SyncBean("SCM_SUP", "PK_SUP_ID", SyncBean.SYNCTYPE_MD_ZB);
		SyncBean instg = new SyncBean("SCM_IN_STG", "PK_INSTG_ID", SyncBean.SYNCTYPE_MD);
		List<SyncBean> instgs = new ArrayList<SyncBean>();
		instgs.add(new SyncBean("SCM_INSTG_PRD", "FK_INSTG_ID", "PK_INSTG_ID"));
		instg.setRelations(instgs);

		SyncBean supAgm = new SyncBean("SCM_SUP_AGM", "PK_SUPAGM_ID", SyncBean.SYNCTYPE_MD_ZB);
		List<SyncBean> supAgms = new ArrayList<SyncBean>();
		supAgms.add(new SyncBean("SCM_SUP_PRD", "FK_SUPAGM_ID", "PK_SUPAGM_ID"));
		supAgm.setRelations(supAgms);

		SyncBean wfmauth = new SyncBean("WFM_AUTHORIZE", "PK_AUTH_ID", SyncBean.SYNCTYPE_MD_ZB);
		SyncBean holiday = new SyncBean("SYS_HOLIDAY", "PK_HLD_ID", SyncBean.SYNCTYPE_MD_ZB);
		SyncBean outstg = new SyncBean("SCM_OUT_STG", "PK_OUTSTG_ID", SyncBean.SYNCTYPE_MD);
		List<SyncBean> outstgs = new ArrayList<SyncBean>();
		outstgs.add(new SyncBean("SCM_OUTSTG_PRD", "FK_OUTSTG_ID", "PK_OUTSTG_ID"));
		outstg.setRelations(outstgs);

		SyncBean tsk = new SyncBean("SCM_TAKE_STK", "PK_TSK_ID", "TAKE_TIME", SyncBean.SYNCTYPE_MD);
		List<SyncBean> tsks = new ArrayList<SyncBean>();
		tsks.add(new SyncBean("SCM_TAKESTK_PRD", "FK_TSK_ID", "PK_TSK_ID"));
		tsk.setRelations(tsks);

		SyncBean smscont = new SyncBean("SMS_CONT", "PK_SMSCONT_ID", SyncBean.SYNCTYPE_MD);
		SyncBean batch = new SyncBean("SMS_BATCH", "PK_BATCH_ID", SyncBean.SYNCTYPE_MD);
		List<SyncBean> batchs = new ArrayList<SyncBean>();
		batchs.add(new SyncBean("SMS_BATCH_COND", "FK_BATCH_ID", "PK_BATCH_ID"));
		batch.setRelations(batchs);
		SyncBean outbox = new SyncBean("SMS_OUTBOX", "PK_SMS_ID", SyncBean.SYNCTYPE_MD);

		SyncBean attfile = new SyncBean("SYS_ATT_FILE", "PK_ATTFILE_ID", SyncBean.SYNCTYPE_MD_ZB);

		CONSTANTLY.add(role);
		CONSTANTLY.add(usr);
		CONSTANTLY.add(prdUnit);
		CONSTANTLY.add(prdCate);
		CONSTANTLY.add(sup);
		CONSTANTLY.add(instg);
		CONSTANTLY.add(outstg);
		CONSTANTLY.add(tsk);
		CONSTANTLY.add(smscont);
		CONSTANTLY.add(batch);
		CONSTANTLY.add(outbox);

		CONSTANTLY.add(supAgm);
		CONSTANTLY.add(prd);
		CONSTANTLY.add(gblprdPrice);
		CONSTANTLY.add(wfmauth);
		CONSTANTLY.add(holiday);
		CONSTANTLY.add(attfile);
	}

	/**
	 * 一天同步表
	 */
	static {
		EVERY_DAY.add(new SyncBean("CUS_GUA", "PK_GUA_ID", SyncBean.SYNCTYPE_MD));
		// SyncBean para = new SyncBean("SYS_PARA", "PK_PARA_ID",
		// SyncBean.SYNCTYPE_MD);

		SyncBean mem = new SyncBean("CUS_MEM", "PK_MEM_ID", SyncBean.SYNCTYPE_MD);
		List<SyncBean> guas = new ArrayList<SyncBean>();
		SyncBean gua = new SyncBean("CUS_REL", "FK_MEM_ID", "PK_MEM_ID");
		SyncBean sales = new SyncBean("CUS_MEM_SALES", "FK_MEM_ID", "PK_MEM_ID");
		SyncBean black = new SyncBean("CUS_MEM_BLACK", "FK_MEM_ID", "PK_MEM_ID");
		guas.add(gua);
		guas.add(sales);
		guas.add(black);
		mem.setRelations(guas);

		EVERY_DAY.add(mem);
		OrderBy orderby = new OrderBy("OPERATE_TIME", "ASC");
		SyncBean order = new SyncBean("POS_ORDER", "PK_ORDER_ID", SyncBean.SYNCTYPE_MD, orderby);
		List<SyncBean> orders = new ArrayList<SyncBean>();
		orders.add(new SyncBean("POS_ORDER_PRD", "PK_ORDER_ID", "PK_ORDER_ID"));
		order.setRelations(orders);
		EVERY_DAY.add(order);
		EVERY_DAY.add(new SyncBean("POS_ORDER_PAYMENT", "PK_PAYMENT_ID", SyncBean.SYNCTYPE_MD));
		EVERY_DAY.add(new SyncBean("CUS_MEM_CLASS", "PK_MEMCLASS_ID", SyncBean.SYNCTYPE_MD));
		SyncBean classimp = new SyncBean("CUS_CLASS_IMP", "PK_CLASSIMP_ID", SyncBean.SYNCTYPE_MD);
		List<SyncBean> classimps = new ArrayList<SyncBean>();
		classimps.add(new SyncBean("CUS_CLASS_IMP_DTL", "FK_CLASSIMP_ID", "PK_CLASSIMP_ID"));
		classimp.setRelations(classimps);
		EVERY_DAY.add(classimp);
		EVERY_DAY.add(new SyncBean("PAR_C_TYPE", "PK_PARTYPE_ID", SyncBean.SYNCTYPE_MD));
		EVERY_DAY.add(new SyncBean("PAR_ROOM", "PK_ROOM_ID", SyncBean.SYNCTYPE_MD));
		SyncBean reserve = new SyncBean("PAR_RESERVE", "PK_PARRES_ID", SyncBean.SYNCTYPE_MD);
		List<SyncBean> reserves = new ArrayList<SyncBean>();
		SyncBean room = new SyncBean("PAR_RESERVE_ROOM", "FK_PARRES_ID", "PK_PARRES_ID");
		reserves.add(room);
		reserve.setRelations(reserves);
		EVERY_DAY.add(reserve);
		// EVERY_DAY.add(para);
		EVERY_DAY.add(new SyncBean("CUS_FNG", "PK_FING_ID", SyncBean.SYNCTYPE_MD));
		EVERY_DAY.add(new SyncBean("CUS_MEM_SUSPEND", "PK_SUSPEND_ID", SyncBean.SYNCTYPE_MD));

		EVERY_DAY.add(new SyncBean("GBL_CUS_CPNDEF", "PK_CPNDEF_ID", SyncBean.SYNCTYPE_MD));
		EVERY_DAY.add(new SyncBean("CUS_COUPON", "PK_COUPON_ID", SyncBean.SYNCTYPE_MD));
		EVERY_DAY.add(new SyncBean("CUS_MEM_HIS", "PK_MEMHIS_ID", SyncBean.SYNCTYPE_MD));
		EVERY_DAY.add(new SyncBean("LOC_PER_INOUT", "PK_PER_IO_ID", "OPERATE_TIME", SyncBean.SYNCTYPE_MD));
		EVERY_DAY.add(new SyncBean("SYS_BAR_CODE", "PK_BCODE_ID", SyncBean.SYNCTYPE_MD));
		EVERY_DAY.add(new SyncBean("LOC_PARTY_INOUT", "PK_PARTY_IO_ID", "OPERATE_TIME", SyncBean.SYNCTYPE_MD));
		EVERY_DAY.add(new SyncBean("PAR_OT_TICKET", "PK_OTT_ID", SyncBean.SYNCTYPE_MD));

	}

	public static JdbcTemplate getJdbcTemplate() {
		return SpringUtil.getBeanByType(JdbcTemplate.class);
	}

	public static List<Map<String, Object>> query(String sql, Object... params) {
		return SqlHelper.getJdbcTemplate().queryForList(sql, params);
	}

	public static void query(String sql, RowCallbackHandler handler, Object... params) {
		SqlHelper.getJdbcTemplate().query(sql, params, handler);
	}

	public static void executeUpdate(final String sql, final Object... params) {
		DataSource dataSouce = (DataSource) SpringUtil.getBean("dataSource");
		PlatformTransactionManager transactionManager = new DataSourceTransactionManager(dataSouce);
		TransactionTemplate template = new TransactionTemplate(transactionManager);
		template.execute(new TransactionCallback<Object>() {
			@Override
			public Object doInTransaction(TransactionStatus status) {
				JdbcTemplate jdbcTemplate = SpringUtil.getBeanByType(JdbcTemplate.class);
				jdbcTemplate.update(sql, params);
				return null;
			}
		});
	}

	public static void nonTransactionExcute(final String sql, final Object... params) {
		JdbcTemplate jdbcTemplate = SpringUtil.getBeanByType(JdbcTemplate.class);
		jdbcTemplate.update(sql, params);
	}

	public static void nonTransactionExcute(SqlAndParam sqlAndParam) {
		SqlHelper.nonTransactionExcute(sqlAndParam.getSql(), sqlAndParam.getParams());
	}

	public static void executeUpdate(SqlAndParam sqlAndParam) {
		SqlHelper.executeUpdate(sqlAndParam.getSql(), sqlAndParam.getParams());
	}

	public static void executeUpdate(final SyncTransaction transaction) {
		final DataSource dataSouce = (DataSource) SpringUtil.getBean("dataSource");
		PlatformTransactionManager transactionManager = new DataSourceTransactionManager(dataSouce);
		TransactionTemplate template = new TransactionTemplate(transactionManager);
		template.execute(new TransactionCallback<Object>() {
			@Override
			public Object doInTransaction(TransactionStatus status) {
				transaction.doTransaction(new JdbcTemplate(dataSouce));
				return null;
			}
		});
	}

	public static List<String> getNotOperateTask() {

		final List<String> ids = new ArrayList<String>();

		SqlHelper.getJdbcTemplate().query(SqlHelper.SQL_SELECT_NOT_OPERATE_SYS_DATA_SYNC_LOG, new RowCallbackHandler() {

			@Override
			public void processRow(ResultSet rs) throws SQLException {
				ids.add(rs.getString("DATASYNC_ID"));
			}
		});

		return ids;
	}

	public static synchronized void insertSysDataSyncLog(String taskId) {

		Map<String, Object> map;
		try {
			map = SqlHelper.getJdbcTemplate().queryForMap(SqlHelper.SQL_SELECT_SYS_DATA_SYNC_LOG, taskId);
		} catch (EmptyResultDataAccessException e) {
			map = null;
		}

		if (map == null) {
			List<Object> params = new ArrayList<Object>();
			params.add(taskId);
			params.add(SqlHelper.SYNC_STATUS_FAIL);
			params.add(null);
			params.add(null);
			params.add(SyncParamHelper.CURRENT_STORE);
			params.add(new Date());
			executeUpdate(SqlHelper.SQL_INSERT_SYS_DATA_SYNC_LOG, params.toArray());
		}
	}

	public static synchronized void successConsumer(String taskId) {
		SqlHelper.executeUpdate(SqlHelper.SQL_UPDATE_SYS_DATA_SYNC_LOG, taskId);
	}

	public static synchronized void syncRelationTable(String tableName, SqlAndParam sqlAndParam) {
		SqlHelper.executeUpdate("DELETE FROM " + tableName, new Object[] {});
		SqlHelper.executeUpdate(sqlAndParam.getSql(), sqlAndParam.getParams());
	}

	/**
	 * 
	 * @Desc 表名
	 * @author QUINN
	 * @date 2014年9月18日下午6:21:48
	 * @param tableName
	 * @param date
	 * @return
	 */
	public static void checkIntervalTaskDate(SyncBean sync, Date date, boolean everyDay) {
		JdbcTemplate jdbcTemplate = SpringUtil.getBeanByType(JdbcTemplate.class);
		String sql = "SELECT SYNC_TIME_E FROM  SYS_DATA_SYNC WHERE TABLE_NAME=?  ORDER BY SYNC_TIME_E DESC LIMIT 0,1";
		Date previous;
		try {
			Map<String, Object> map = jdbcTemplate.queryForMap(sql, sync.getTableName());
			previous = (Date) map.get("SYNC_TIME_E");
		} catch (EmptyResultDataAccessException e) {
			previous = null;
		}
		if (previous == null)
			previous = everyDay ? SyncParamHelper.TASK_CYCLE_EVERYDAY_START_DATE
					: SyncParamHelper.TASK_CYCLE_CONSTANTLY_START_DATE;
		List<Date[]> intervalDates = SyncParamHelper.getTaskIntervalDate(date, previous,
				everyDay ? Calendar.HOUR_OF_DAY : Calendar.MINUTE, everyDay ? 1
						: SyncParamHelper.TASK_CYCLE_CONSTANTLY_CYCLE);

		List<List<Date[]>> dats = SyncUtil.breakUpList(intervalDates, 1000);
		for (List<Date[]> iteDates : dats) {
			StringBuffer insertSql = new StringBuffer("INSERT INTO SYS_DATA_SYNC(");
			insertSql.append(SqlHelper.SYS_DATA_SYNC_FLDS).append(")VALUES");
			List<Object> params = new ArrayList<Object>();
			for (Date[] dates : iteDates) {
				insertSql.append("(?,?,?,?,?,?,?,?),");
				String id = PrimaryKeyGenerator.getRandomPrimaryKey();
				params.add(id);
				params.add(sync.getTableName());
				params.add(dates[0]);
				params.add(dates[1]);
				params.add(null);
				params.add(SqlHelper.EXTRACT_FLAG_NO);
				params.add(SyncParamHelper.CURRENT_STORE);
				params.add(new Date());
			}
			insertSql.delete(insertSql.length() - 1, insertSql.length());
			SqlHelper.executeUpdate(insertSql.toString(), params.toArray());
		}
	}
}
